package com.zhuang.convert.srevice;

import com.zhuang.convert.model.ColumnValues;
import com.zhuang.convert.model.TableColumns;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by zhuanglei on 15/4/26.
 */
public class ConvertService {

    private final File sourceFile;

    public ConvertService(File sourceFile) {
        this.sourceFile = sourceFile;
    }

    public TableColumns getInsertColumns() {

        TableColumns tableColumns = new TableColumns();
        try {
            Workbook book = Workbook.getWorkbook(sourceFile);
            String tableName = sourceFile.getName().split("\\.")[0];
            Sheet sheet = book.getSheet(0);
            int columnNum = sheet.getColumns();

            ArrayList<String> columns = new ArrayList<String>();

            for (int i = 0; i < columnNum; i++) {
                columns.add(sheet.getCell(i, 0).getContents().trim());
            }

            tableColumns.setTableName(tableName);
            tableColumns.setColumns(columns);
            book.close();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (BiffException e) {
            e.printStackTrace();
        }

        return tableColumns;
    }

    public List<ColumnValues> geColumnValues() {
        ArrayList<ColumnValues> columnValues = new ArrayList<ColumnValues>();
        try {
            Workbook book = Workbook.getWorkbook(sourceFile);
            Sheet sheet = book.getSheet(0);
            int columnNum = sheet.getColumns();
            int rows = sheet.getRows();

            if (rows <= 1) {
                return null;
            }

            for (int i = 1; i < rows; i++) {
                ColumnValues columnValue = new ColumnValues();
                for (int j = 0; j < columnNum; j++) {
                    if (sheet.getCell(j, i).getContents().trim().equals("")) {
                        break;
                    } else {
                        columnValue.addValue(sheet.getCell(j, i).getContents().trim());
                    }

                }

                columnValues.add(columnValue);

            }

            return columnValues;
        } catch (IOException e) {
            e.printStackTrace();
        } catch (BiffException e) {
            e.printStackTrace();
        }
        return columnValues;

    }

    public List<String> getInsertValues() {
        ArrayList<String> stringValues = new ArrayList<String>();
        List<ColumnValues> columnValueses = geColumnValues();
        for (ColumnValues columnValuese : columnValueses) {
            List<String> columnValue = columnValuese.getColumnValue();
            String result = "";
            for (String s : columnValue) {
                result += "\"" + s + "\",";
            }

            if (result.length() > 0) {
                result = result.substring(0, result.length() - 1);
            }
            stringValues.add(result);

        }

        return stringValues;

    }

    public List<String> getSql() {
        ArrayList<String> stringValues = new ArrayList<String>();
        TableColumns tableColumn = getInsertColumns();
        List<String> strings = getInsertValues();
        List<String> result = new ArrayList<String>();

        for (String s : strings) {
            String sql = " insert into " + tableColumn.getTableName()
                    + "(" + tableColumn.getColumnStr() + ") values ( "
                    + s + ");";
            result.add(sql);
        }

        return result;
    }

    public void writeToFile(String path,List<String> sqls){
        try {
            new File(path).createNewFile();

            FileWriter fileWriter = new FileWriter(path);
            for (String sql : sqls) {
                fileWriter.write(sql + "\n");
            }
            fileWriter.close();

        } catch (IOException e) {
            e.printStackTrace();
        }

    }

}
